package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;

import controlador.Sistema;

import negocio.Sede;
import negocio.Vehiculo;

public class perVehiculo {
	private Sistema sist;
	private int max;
	public Vehiculo select(String nombre)
	{
		sist=Sistema.getInstance();
		Vehiculo buscado=null;
		try {			
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps=con.prepareStatement("Select * from Vehiculo where dominio=?");
			ps.setString(1,nombre);			
			ResultSet rs=ps.executeQuery();
			if(rs.next())
			{
				buscado=new Vehiculo();
				buscado.setKmsi(rs.getFloat(3));
				buscado.setKms(rs.getFloat(4));
				buscado.setEstadoAct(rs.getBoolean(5));
				buscado.setEstado(rs.getBoolean(6));
				buscado.setEnViaje(rs.getBoolean(9));
				buscado.setGastos(rs.getFloat(10));
				buscado.setCombustible(rs.getString(11));
				buscado.setModelo(rs.getString(12));
				buscado.setTipo(rs.getString(13));
				buscado.setDominio(nombre);
				PreparedStatement ps2=con.prepareStatement("Select NOMBRE from Sede where IDSEDE=?");
				ps2.setInt(1, rs.getInt(7));
				ResultSet rs2=ps2.executeQuery();
				if(rs2.next())
				{
					buscado.setSede(sist.buscarSede(rs2.getString(1)));
				}
				PreparedStatement ps3=con.prepareStatement("Select NOMBRE from Sede where IDSEDE=?");
				ps2.setInt(1, rs.getInt(8));
				ResultSet rs3=ps2.executeQuery();
				if(rs3.next())
				{
					buscado.setSedeAct(sist.buscarSede(rs3.getString(1)));
				}
			}
			PoolConnection.getPoolConnection().releaseConnection(con);
			return buscado;
		} catch (SQLException e) {
			e.getMessage();
			e.printStackTrace();			
		}
		return null;
		
	}
	public Vector<Vehiculo> select()
	{
		sist=Sistema.getInstance();
		Vehiculo buscado=null;
		Vector<Vehiculo> v=new Vector<Vehiculo>();
		try {			
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps=con.prepareStatement("Select * from Vehiculo");		
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				buscado=new Vehiculo();
				buscado.setKmsi(rs.getFloat(3));
				buscado.setKms(rs.getFloat(4));
				buscado.setEstadoAct(rs.getBoolean(5));
				buscado.setEstado(rs.getBoolean(6));
				buscado.setEnViaje(rs.getBoolean(9));
				buscado.setGastos(rs.getFloat(10));
				buscado.setCombustible(rs.getString(11));
				buscado.setModelo(rs.getString(12));
				buscado.setTipo(rs.getString(13));
				buscado.setDominio(rs.getString(2));
				PreparedStatement ps2=con.prepareStatement("Select NOMBRE from Sede where IDSEDE=?");
				ps2.setInt(1, rs.getInt(7));
				ResultSet rs2=ps2.executeQuery();
				if(rs2.next())
				{
					buscado.setSede(sist.buscarSede(rs2.getString(1)));
				}
				PreparedStatement ps3=con.prepareStatement("Select NOMBRE from Sede where IDSEDE=?");
				ps2.setInt(1, rs.getInt(8));
				ResultSet rs3=ps2.executeQuery();
				if(rs3.next())
				{
					buscado.setSedeAct(sist.buscarSede(rs3.getString(1)));
				}
				v.add(buscado);
			}
			PoolConnection.getPoolConnection().releaseConnection(con);
			
		} catch (SQLException e) {
			e.getMessage();
			e.printStackTrace();			
		}
		return v;
		
	}
	public void delete(String dominio)
	{
	
		try {
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps;
			ps = con.prepareStatement("delete from Vehiculo where dominio=?");
			ps.setString(1, dominio);
			ps.execute();
			PoolConnection.getPoolConnection().releaseConnection(con);
		} catch (SQLException e) {
			e.getMessage();
			e.printStackTrace();
		}		
	}
	public void update(Vehiculo v)
	{
	
		try {
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("update Vehiculo set KMSI=?, KMS=?, ESTADOACT=?, ESTADO=?, " +
					"IDSEDE=?, IDSEDEACT=?, ENVIAJE=?, GASTO=?, COMBUSTIBLE=?, MODELO=?, TIPO=? where DOMINIO=?");
			ps.setFloat(1, v.getKmsi());
			ps.setFloat(2, v.getKms());
			if(v.isEstadoAct())
				ps.setBoolean(3,true);
			else
				ps.setBoolean(3,false);
			if(v.isEstado())
				ps.setBoolean(4, true);
			else
				ps.setBoolean(4, false);
			ps.setInt(5, v.getSede().getNroSede());
			ps.setInt(6,v.getSedeAct().getNroSede());
			if(v.isEnViaje())
				ps.setBoolean(7, true);
			else
				ps.setBoolean(7, false);
			ps.setFloat(8, v.getGastos());
			ps.setString(9, v.getCombustible());
			ps.setString(10, v.getModelo());
			ps.setString(11, v.getTipo());
			ps.setString(12, v.getDominio());
			ps.execute();
			PoolConnection.getPoolConnection().releaseConnection(con);
		} catch (SQLException e) {
			e.getMessage();
			e.printStackTrace();
		}		
	}
	public void Insert(Vehiculo v)
	{
		
		try {
			Connection con=PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps=con.prepareStatement("Select max(ID_VEHICULO) from Vehiculo");
			ResultSet rs=ps.executeQuery();			
			if(rs.next())
			{
				max=rs.getInt(1);
			}
			PreparedStatement ps2=con.prepareStatement("Insert into Vehiculo values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
			ps2.setInt(1,max+1);
			ps2.setString(2,v.getDominio());
			ps2.setFloat(3, v.getKmsi());
			ps2.setFloat(4, v.getKms());
			ps2.setBoolean(5, v.isEstadoAct());
			ps2.setBoolean(6,v.isEstado());
			ps2.setInt(7,v.getSede().getNroSede());
			ps2.setInt(8, v.getSede().getNroSede());
			ps2.setBoolean(9,v.isEnViaje());
			ps2.setFloat(10,v.getGastos());
			ps2.setString(11,v.getCombustible());
			ps2.setString(12,v.getModelo());
			ps2.setString(13, v.getTipo());
			ps2.execute();
			PoolConnection.getPoolConnection().releaseConnection(con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}
